﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_HO_SO_THAM_DINH_Search]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sproc_HO_SO_THAM_DINH_Search]
GO
 
CREATE PROCEDURE [dbo].[sproc_HO_SO_THAM_DINH_Search]
	@SO_PHIEU NVARCHAR(255),
	@TRANGTHAI CHAR(1), /* Y N T*/
	@TEN_CHU_SU_DUNG NVARCHAR(255),
	@QUANID INT, /* null : -1 */
	@PHUONGID INT, /* null : -1 */
	@DUONG NVARCHAR(50),
	@DOANDUONG NVARCHAR(50),
	@MIN_PRICE FLOAT,
	@MAX_PRICE FLOAT,
	@PageSize INT,
	@PageNo INT -- di tu 1
AS
BEGIN
	
	DECLARE @FromIndex INT
	DECLARE @ToIndex INT
	-- di tu 1
	SET @FromIndex = @PageSize * (@PageNo - 1) + 1
	SET @ToIndex = @PageSize * @PageNo
			   
	SELECT 
		RowNumber ,
		RowTotal,
		MA_HO_SO,
		DUONG,
		DOAN_DUONG,
		TEN_QUAN,
		TEN_PHUONG,
		SO_PHIEU,
		TEN_CHU_SU_DUNG,
		DIA_CHI,
		NGAY_KHOI_TAO,
		NGAY_CAP_NHAT,
		UserName,
		GIA_DAT,
		GIA_GIAO_DICH_TRIEU_DONG,
		GIA_DAT_1,
		GIA_DAT_2,
		GIA_DAT_BINH_QUAN_1,
		GIA_DAT_BINH_QUAN_2,
		ACTIVE_FLAG
	FROM (
		SELECT 
				ROW_NUMBER() OVER(ORDER BY hstd.SO_PHIEU,hstd.NGAY_CAP_NHAT DESC) AS RowNumber,
				COUNT(0) OVER() AS RowTotal,
				hstd.MA_HO_SO,
				hstd.SO_PHIEU,
				dks.DUONG,
				dks.DOAN_DUONG,
				tsqquan.NOI_DUNG AS TEN_QUAN,
				tsphuong.NOI_DUNG AS TEN_PHUONG,
				hstd.TEN_CHU_SU_DUNG,
				hstd.DIA_CHI,
				hstd.NGAY_KHOI_TAO,
				hstd.NGAY_CAP_NHAT,
				au.UserName,
				gdd.GIA_DAT,
				gtd.GIA_GIAO_DICH_TRIEU_DONG,
				hstd.GIA_DAT_1,
				hstd.GIA_DAT_2,
				hstd.GIA_DAT_BINH_QUAN_1,
				hstd.GIA_DAT_BINH_QUAN_2,
				hstd.ACTIVE_FLAG
		FROM   HO_SO_THAM_DINH AS hstd
				INNER JOIN DUONG_KHAO_SAT AS dks
					ON  hstd.MA_DKS = dks.MA_DKS
				INNER JOIN THAM_SO tsqquan
					ON  dks.QUAN = tsqquan.MA_THAM_SO
				INNER JOIN THAM_SO AS tsphuong
					ON  dks.PHUONG = tsphuong.MA_THAM_SO
				INNER JOIN aspnet_Users au
					ON hstd.NGUOI_KHOI_TAO = au.UserId
				INNER JOIN GIA_THAM_DINH gtd
					ON gtd.MA_HO_SO = hstd.MA_HO_SO
				LEFT JOIN GIA_DOAN_DUONG gdd
					ON gdd.MA_DKS = dks.MA_DKS
		WHERE 				
				isnull(hstd.SO_PHIEU,'')
				LIKE 
				CASE WHEN @SO_PHIEU IS NOT NULL AND @SO_PHIEU <> '' THEN '%' + @SO_PHIEU + '%' ELSE isnull(hstd.SO_PHIEU,'') END
			AND
				isnull(hstd.TEN_CHU_SU_DUNG,'')
				LIKE 
				CASE WHEN @TEN_CHU_SU_DUNG IS NOT NULL AND @TEN_CHU_SU_DUNG <> '' THEN '%' + @TEN_CHU_SU_DUNG + '%' ELSE isnull(hstd.TEN_CHU_SU_DUNG,'') END
			AND
				tsqquan.MA_THAM_SO
				= 
				CASE WHEN @QUANID <> -1 THEN @QUANID ELSE tsqquan.MA_THAM_SO END
			AND
				tsphuong.MA_THAM_SO
				= 
				CASE WHEN @PHUONGID <> -1 THEN @PHUONGID ELSE tsphuong.MA_THAM_SO END
			AND
				isnull(dks.DUONG,'')
				LIKE 
				CASE WHEN @DUONG IS NOT NULL AND @DUONG <> '' THEN '%' + @DUONG + '%' ELSE isnull(dks.DUONG,'') END
			AND
				isnull(dks.DOAN_DUONG,'')
				LIKE 
				CASE WHEN @DOANDUONG IS NOT NULL AND @DOANDUONG <> '' THEN '%' + @DOANDUONG + '%' ELSE isnull(dks.DOAN_DUONG,'') END					
			AND
				isnull(hstd.ACTIVE_FLAG,'')
				<>
				CASE WHEN @TRANGTHAI IS NOT NULL AND @TRANGTHAI = '' THEN 'N' ELSE '' END
			AND 
				isnull(hstd.ACTIVE_FLAG,'')
				=
				CASE WHEN @TRANGTHAI IS NOT NULL AND @TRANGTHAI <> '' THEN @TRANGTHAI ELSE isnull(hstd.ACTIVE_FLAG,'') END
			AND gtd.GIA_GIAO_DICH_TRIEU_DONG 
				>=
				CASE WHEN @MIN_PRICE <> 0 THEN @MIN_PRICE ELSE 0 END
			AND gtd.GIA_GIAO_DICH_TRIEU_DONG 
				<=
				CASE WHEN @MAX_PRICE <> 0 THEN @MAX_PRICE ELSE gtd.GIA_GIAO_DICH_TRIEU_DONG END
			AND (gdd.NGAY_CAP_NHAT
				=
				(SELECT MAX(NGAY_CAP_NHAT) FROM GIA_DOAN_DUONG gdd2 WHERE gdd2.MA_DKS = dks.MA_DKS)
			OR gdd.NGAY_CAP_NHAT IS NULL)
	)  AS rownumber
	WHERE RowNumber BETWEEN @FromIndex AND @ToIndex OR @PageNo = 0
END